
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE view [dbo].[TMAUXI_EMPR] AS                
Select                 
 T1.CO_EMPR , 'C' TI_AUXI_EMPR, T1.CO_CLIE CO_AUXI_EMPR, T1.NO_CLIE NO_AUXI,                 
 T3.DE_DIRE NO_DIRE, T1.NU_RUCS, T3.NU_TLF1 NU_TLFN, T3.NU_FAXS NU_FAXE, T2.CO_TIPO_CLIE TI_CLIE, T2.DE_TIPO_CLIE DE_TIPO_CLIE,                
 T3.CO_UBIC_GEOG CO_UBIC_GEOG, '' CO_COND_PAGO, '' CO_MONE_CRED,                 
 T1.IM_LIMI_CRED, 0 IM_CRED_EXED, '' DE_OBSE, '' ST_RETE, '' ST_BCON, '' ST_SUJE_DETR,                 
 T1.CO_DOCU_SUNA, T1.NU_DOCU_SUNA, T1.TI_PERS_SUNA, T1.NU_RETE_SUNA,                
 T1.AP_PATE_CLIE AP_PATE_AUXI, T1.AP_MATE_CLIE AP_MATE_AUXI, T1.NO_NATU_0001 NO_NATU_AUX1,                 
 T1.NO_NATU_0002 NO_NATU_AUX2, T1.NO_RAZO_SOCI,     
T1.CO_ZONA,    
'' as CO_SUBR,    
T1.TI_DOCU_IDEN,    
T1.NU_DOCU_IDEN,    
T1.TI_SITU,    
'N' as ST_PERC,    
'' as NU_CNTA_DETR,    
T1.CO_GRUP_ECON,    
'N' as ST_PROV_NOHA,    
'' as TI_CNTA_MNAC,    
'' as NU_CNTA_MNAC,    
'' as TI_CNTA_MEXT,    
'' as NU_CNTA_MEXT        
From  [TMCLIE] T1,                 
      OFIVENT.DBO.[TTTIPO_CLIE] T2,                 
      OFIVENT.DBO.[TDDIRE_CLIE] T3               
WHERE T1.CO_TIPO_CLIE = T2.CO_TIPO_CLIE                
AND T1.CO_EMPR = T3.CO_EMPR                
AND T1.CO_CLIE = T3.CO_CLIE                
AND T3.TI_DIRE = 'COM'        
Union All                 
Select                 
 T1.CO_EMPR , 'C' TI_AUXI_EMPR, T1.CO_CLIE CO_AUXI_EMPR, T1.NO_CLIE NO_AUXI,                 
 '' NO_DIRE, T1.NU_RUCS , '' NU_TLFN, '' NU_FAXE, T2.CO_TIPO_CLIE TI_CLIE, T2.DE_TIPO_CLIE DE_TIPO_CLIE,                
 '' CO_UBIC_GEOG, '' CO_COND_PAGO, '' CO_MONE_CRED,                 
 0 IM_LIMI_CRED, 0 IM_CRED_EXED, '' DE_OBSE, '' ST_RETE, '' ST_BCON, '' ST_SUJE_DETR,                 
 T1.CO_DOCU_SUNA, T1.NU_DOCU_SUNA, T1.TI_PERS_SUNA, T1.NU_RETE_SUNA,                
 T1.AP_PATE_CLIE AP_PATE_AUXI, T1.AP_MATE_CLIE AP_MATE_AUXI, T1.NO_NATU_0001 NO_NATU_AUX1,                 
 T1.NO_NATU_0002 NO_NATU_AUX2, T1.NO_RAZO_SOCI,     
T1.CO_ZONA,    
'' as CO_SUBR,    
T1.TI_DOCU_IDEN,    
T1.NU_DOCU_IDEN,    
T1.TI_SITU,    
'N' as ST_PERC,    
'' as NU_CNTA_DETR,    
T1.CO_GRUP_ECON,    
'N' as ST_PROV_NOHA,    
'' as TI_CNTA_MNAC,    
'' as NU_CNTA_MNAC,    
'' as TI_CNTA_MEXT,    
'' as NU_CNTA_MEXT    
From  [TMCLIE] T1,                 
      OFIVENT.DBO.[TTTIPO_CLIE] T2                
WHERE T1.CO_TIPO_CLIE = T2.CO_TIPO_CLIE                
AND NOT EXISTS (SELECT T3.CO_CLIE                
  FROM OFIVENT.DBO.[TDDIRE_CLIE] T3                
  WHERE T1.CO_EMPR = T3.CO_EMPR                
  AND T1.CO_CLIE = T3.CO_CLIE                
  AND T3.TI_DIRE = 'COM')        
Union All                 
Select                 
 T1.CO_EMPR , 'P' , T1.CO_PROV , T1.NO_CORT_PROV NO_AUXI,                 
 T3.DE_DIRE, T1.NU_RUCS_PROV, T3.NU_TLF1, T3.NU_FAXS, T2.TI_PROV, T2.DE_TIPO_PROV ,                
 '' , t1.CO_COND_PAGO , t1.CO_MONE_FACT ,                 
 0 , 0 , '' , T1.ST_RETE , T1.ST_BCON , T1.ST_SUJE_DETR ,                
 T1.CO_DOCU_SUNA, T1.NU_DOCU_SUNA, T1.TI_PERS_SUNA, T1.NU_RETE_SUNA,                
 T1.AP_PATE_PROV AP_PATE_AUXI, T1.AP_MATE_PROV AP_MATE_AUXI, T1.NO_PROV_NATU NO_NATU_AUX1,         
 T1.NO_PROV_0002 NO_NATU_AUX2, T1.DE_RAZO_SOCI NO_RAZO_SOCI,     
'' AS CO_ZONA,    
'' AS CO_SUBR,    
T1.TI_DOCU_IDEN,    
T1.NU_DOCU_IDEN,    
T1.TI_SITU,    
T1.ST_PERC,    
T1.NU_CNTA_DETR,    
'' AS CO_GRUP_ECON,    
T1.ST_PROV_NOHA,    
T1.TI_CNTA_MNAC,    
T1.NU_CNTA_MNAC,    
T1.TI_CNTA_MEXT,    
T1.NU_CNTA_MEXT    
From  [OFILOGI].[DBO].[TMPROV] T1,                             
      [OFILOGI].[DBO].[TTTIPO_PROV] T2,              
      [OFILOGI].[DBO].[TDDIRE_PROV] T3                            
WHERE T1.TI_PROV = T2.TI_PROV              
AND T1.CO_EMPR = T3.CO_EMPR                            
AND T1.CO_PROV = T3.CO_PROV                            
AND T3.TI_DIRE = 'COM'      
Union All                
Select                 
 T1.CO_EMPR , 'P' , T1.CO_PROV , T1.NO_CORT_PROV NO_AUXI,                 
 '', T1.NU_RUCS_PROV, '', '', T2.TI_PROV, T2.DE_TIPO_PROV ,                
 '' , t1.CO_COND_PAGO , t1.CO_MONE_FACT ,                 
 0 , 0 , '' , T1.ST_RETE , T1.ST_BCON , T1.ST_SUJE_DETR ,                
 T1.CO_DOCU_SUNA, T1.NU_DOCU_SUNA, T1.TI_PERS_SUNA, T1.NU_RETE_SUNA,                
 T1.AP_PATE_PROV AP_PATE_AUXI, T1.AP_MATE_PROV AP_MATE_AUXI, T1.NO_PROV_NATU NO_NATU_AUX1,         
 T1.NO_PROV_0002 NO_NATU_AUX2, T1.DE_RAZO_SOCI NO_RAZO_SOCI,     
'' AS CO_ZONA,    
'' AS CO_SUBR,    
T1.TI_DOCU_IDEN,    
T1.NU_DOCU_IDEN,    
T1.TI_SITU,    
T1.ST_PERC,    
T1.NU_CNTA_DETR,    
'' AS CO_GRUP_ECON,    
T1.ST_PROV_NOHA,    
T1.TI_CNTA_MNAC,    
T1.NU_CNTA_MNAC,    
T1.TI_CNTA_MEXT,    
T1.NU_CNTA_MEXT    
From  [OFILOGI].[DBO].[TMPROV] T1,                             
      [OFILOGI].[DBO].[TTTIPO_PROV] T2                          
WHERE T1.TI_PROV = T2.TI_PROV              
AND NOT EXISTS (SELECT T3.CO_PROV                
    FROM [OFILOGI].[DBO].[TDDIRE_PROV] T3                
    WHERE T1.CO_EMPR = T3.CO_EMPR                
    AND T1.CO_PROV = T3.CO_PROV                
    AND T3.TI_DIRE = 'COM')    
    
Union All                
Select                
CO_EMPR , TI_AUXI_EMPR , CO_AUXI_EMPR, NO_AUXI, NO_DIRE , NU_RUCS, NU_TLFN, NU_FAXE, '', '', '', '', '', 0, 0,         
 '', '', '', '','','','','', '', '', '', '', '',     
 '' as CO_ZONA,    
 '' as CO_SUBR,    
 '' as TI_DOCU_IDEN,    
 '' as NU_DOCU_IDEN,    
 TI_SITU,    
 '' as ST_PERC,    
 '' as NU_CNTA_DETR,    
 '' as CO_GRUP_ECON,    
 '' as ST_PROV_NOHA,    
 '' as TI_CNTA_MNAC,    
 '' as NU_CNTA_MNAC,    
 '' as TI_CNTA_MEXT,    
 '' as NU_CNTA_MEXT    
From [OFISEGU].[DBO].[TMAUXI]                
where TI_AUXI_EMPR NOT IN ('C', 'P')    
Union All                   
Select                 
 T1.CO_EMPR, 'V', T1.CO_VEND, T1.NO_VEND, '', CO_VEND , '', '', '', '', '', '', '', 0, 0, '', '', '', '', '', '',         
 '', '', '', '', '', '', '',     
 '' as CO_ZONA,    
 '' as CO_SUBR,    
 '' as TI_DOCU_IDEN,    
 '' as NU_DOCU_IDEN,    
 TI_SITU,    
 '' as ST_PERC,    
 '' as NU_CNTA_DETR,    
 '' as CO_GRUP_ECON,    
 '' as ST_PROV_NOHA,    
 '' as TI_CNTA_MNAC,    
 '' as NU_CNTA_MNAC,    
 '' as TI_CNTA_MEXT,    
 '' as NU_CNTA_MEXT    
From [TTVEND_TESO] T1        
union All          
Select             
 T1.CO_EMPR, 'E', T1.CO_EMPL, T1.NO_APEL_PATE+ ' '+ T1.NO_APEL_MATE+ ' ' +T1.NO_EMPL ,     
 '', '' , ''  ,'' , '', '', '', '', '', 0, 0,     
'', '', '', '', '', '',         
 '', '', '', '', '', '', '',     
 '' as CO_ZONA,    
 '' as CO_SUBR,    
 '' as TI_DOCU_IDEN,    
 '' as NU_DOCU_IDEN,    
 TI_SITU,    
 '' as ST_PERC,    
 '' as NU_CNTA_DETR,    
 '' as CO_GRUP_ECON,    
 '' as ST_PROV_NOHA,    
 '' as TI_CNTA_MNAC,    
 '' as NU_CNTA_MNAC,    
 '' as TI_CNTA_MEXT,    
 '' as NU_CNTA_MEXT    
From [TMEMPL_TESO] T1


GO
